# -*- coding:utf-8 -*-
"""
Author：Administrator
Date:2021年10月14日
"""
import pandas as pd
from docx import Document
from docx.oxml.ns import qn
from docx.enum.table import WD_CELL_VERTICAL_ALIGNMENT, WD_TABLE_ALIGNMENT  # 导入单元格垂直对齐
from docx.enum.text import WD_PARAGRAPH_ALIGNMENT # 导入段落对齐
from docx.shared import Cm, Pt

import numpy as np

## 1.提取楼层剪力
path = './数据/附加阻尼比计算原始数据.xlsx'

data_sf = pd.read_excel(path, sheet_name='楼层剪力', skiprows=2, usecols=[0, 2, 3, 4], names=['层', '时程', 'X向剪力(kN)',
																						  'Y向剪力(kN)'])

# 去除不包含屋顶行的数据
data_sf = data_sf[~data_sf['层'].str.contains('屋顶')]
# 筛选只包含'全部'的数据行
con = data_sf['时程'].str.contains('全部')
data_sf = data_sf[con].reset_index(drop=True)

data_sf.loc[data_sf['时程'].str.contains('X'), 'Vi(kN)'] = data_sf['X向剪力(kN)']
data_sf.loc[data_sf['时程'].str.contains('Y'), 'Vi(kN)'] = data_sf['Y向剪力(kN)']

data_sf['Vi(kN)'] = abs(data_sf['Vi(kN)'])
data_sf = data_sf.drop(labels=['X向剪力(kN)', 'Y向剪力(kN)'], axis=1)
# print(data_sf)
#
# data_sf.to_excel('./数据/01结果-剪力.xlsx', index=False)


##2.提取X向层间位移
data_xd = pd.read_excel(path, sheet_name='X向位移角', skiprows=1, usecols=[0, 1, 6], names=['时程', '层', 'Δui(mm)'])
# 筛选只包含'X(全部)'的数据行
con = data_xd['时程'].str.contains('X\(全部\)', regex=True)
data_xd = data_xd[con].reset_index(drop=True)
data_xd['Δui(mm)'] = abs(data_xd['Δui(mm)'])

##3.提取Y向层间位移
data_yd = pd.read_excel(path, sheet_name='Y向位移角', skiprows=1, usecols=[0, 1, 6], names=['时程', '层', 'Δui(mm)'])
# 筛选只包含'X(全部)'的数据行
con = data_yd['时程'].str.contains('Y\(全部\)', regex=True)
data_yd = data_yd[con].reset_index(drop=True)
data_yd['Δui(mm)'] = abs(data_yd['Δui(mm)'])
# print(data_yd)
# data_yd.to_excel('./数据/03结果-Y向位移.xlsx', index=False)

##4.合并XY向层间位移

data_xd = pd.concat([data_xd, data_yd])
data_xd.reset_index(inplace=True, drop=True)

# print(data_xd)
#
# data_dis.to_excel('./数据/04结果-XY向位移.xlsx')

##5. 合并剪力与位移数据
data_yd = pd.merge(data_sf, data_xd, on=['层', '时程'])
data_yd['Wsi(kN·mm)'] = (0.5 * data_yd['Vi(kN)'] * data_yd['Δui(mm)']).map(lambda x: "%.2f" % x)
data_yd['Δui(mm)'] = data_yd['Δui(mm)'].map(lambda x: "%.2f" % x)
data_yd['Vi(kN)'] = data_yd['Vi(kN)'].astype('int')
data_yd.set_index('层', inplace=True)

##5.读取连接单元数据
data_link = pd.read_excel(path, sheet_name='连接单元数据', skiprows=2, usecols=[0, 1, 4, 6, 8], names=['连接单元号', '时程', 'DX',
																								 'DY', 'DZ'])

# 筛选只包含'(全部)'的数据行
con = data_link['时程'].str.contains('全部')
data_link = data_link[con].reset_index(drop=True)

data_lx = pd.read_excel(path, sheet_name='X向阻尼器编号')
data_ly = pd.read_excel(path, sheet_name='Y向阻尼器编号')

table_name = list(data_link['时程'].drop_duplicates())


def Force(data):
	d = data['阻尼器变形(mm)']
	F = data['屈服力/摩擦荷载(kN)']
	k = data['初始刚度(kN/mm)']
	Rotio = data['第二刚度比(kN/mm)']
	dy = data['屈服/起滑位移(mm)']
	if d >= dy:
		Force = F + Rotio * k * (d - dy)
	else:
		Force = d * k
	return Force


def Wci(data):
	d = data['阻尼器变形(mm)']
	F = data['屈服力/摩擦荷载(kN)']
	k = data['初始刚度(kN/mm)']
	Rotio = data['第二刚度比(kN/mm)']
	dy = data['屈服/起滑位移(mm)']
	Fu = data['阻尼器出力(kN)']
	if d >= dy:
		Wci = 4 * (F * d - Fu * dy)
	else:
		Wci = 0

	return Wci


def doctable(data, tabletitle, pathfile):
	document = Document()
	data = pd.DataFrame(data)  # My input data is in the 2D list form
	document.add_heading(tabletitle)
	table = document.add_table(rows=(data.shape[0]), cols=data.shape[1])  # First row are table headers!
	table.alignment = WD_TABLE_ALIGNMENT.CENTER  # 表格居中
	for i, column in enumerate(data):
		for row in range(data.shape[0]):
			table.cell(row, i).text = str(data[column][row])
	document.save(pathfile)


def xlsx2docx(data):
	rows = data.shape[0]
	columns = data.shape[1]
	# doc = Document('./附加阻尼比计算.docx')
	# doc.styles['Normal'].font.name = u'Times New Roman'

	# doc.styles['Normal']._element.rPr.rFonts.set(qn('w:eastAsia'), u'宋体')
	table = doc.add_table(rows, columns, style='Table Grid')
	table.alignment = WD_TABLE_ALIGNMENT.CENTER

	if data.iloc[0, 0] == '阻尼器编号':
		table.autofit = False
		widths = (Cm(5.5), Cm(1.2), Cm(1.3), Cm(1.3), Cm(1.3), Cm(1.3), Cm(1.3), Cm(1.6), Cm(2.5))
		for row in table.rows:
			for idx, width in enumerate(widths):
				row.cells[idx].width = width

	for i in range(rows):
		for j in range(columns):
			if str(data.iloc[i, j]) == 'nan':
				table.cell(i, j).text = ""
			else:
				table.cell(i, j).text = str(data.iloc[i, j])  # 由于里面有数据型的，需要强制转字符
			table.cell(i, j).vertical_alignment = WD_CELL_VERTICAL_ALIGNMENT.CENTER
			table.cell(i, j).paragraphs[0].alignment = WD_PARAGRAPH_ALIGNMENT.CENTER


##5. 拆分表格
table_name = list(data_yd['时程'].drop_duplicates())
# print(table_name)
new_data = pd.ExcelWriter('./数据/09结果-势能和耗能.xlsx')

data_ad = pd.DataFrame(columns=['阻尼器器耗能(KN·mm)', '结构应变能(kN·mm)', '附加阻尼比(%)'])
index_list = list()

for i in table_name:
	index_list.append(i[0:-6])
	data1 = data_yd[data_yd['时程'] == i]
	data1 = data1.drop('时程', axis=1)
	data1.reset_index('层')
	data1 = data1.copy()
	data1['Wsi(kN·mm)'] = data1['Wsi(kN·mm)'].astype('float64')
	data1.loc['势能合计'] = data1[['Wsi(kN·mm)']].agg('sum')
	temp1 = data1.iloc[-1, -1]
	# print(temp1)
	data1.to_excel(new_data, sheet_name=i.replace('全部', '势能'), float_format='%.2f')

	data2 = data_link[data_link['时程'] == i]
	if 'X' in str(i):
		data2 = pd.merge(data2, data_lx, on=['连接单元号'])
	else:
		data2 = pd.merge(data2, data_ly, on=['连接单元号'])

	data2.loc[data2['连接单元方向'] == 1, '阻尼器变形(mm)'] = data2['DX']
	data2.loc[data2['连接单元方向'] == 2, '阻尼器变形(mm)'] = data2['DY']
	data2.loc[data2['连接单元方向'] == 3, '阻尼器变形(mm)'] = data2['DZ']
	data2['阻尼器变形(mm)'] = abs(data2['阻尼器变形(mm)'])
	data2['初始刚度(kN/mm)'] = data2['屈服力/摩擦荷载(kN)'] / data2['屈服/起滑位移(mm)']
	data2['阻尼器出力(kN)'] = data2.apply(Force, axis=1)
	data2['Wci(kN·mm)'] = data2.apply(Wci, axis=1)
	data2 = data2.drop(labels=['时程', 'DX', 'DY', 'DZ', '连接单元方向'], axis=1)

	temp = data2.连接单元号
	data2 = data2.drop('连接单元号', axis=1)
	data2.insert(1, '连接单元号', temp)

	temp = data2['初始刚度(kN/mm)']
	data2 = data2.drop('初始刚度(kN/mm)', axis=1)
	data2.insert(4, '初始刚度(kN/mm)', temp)

	data2 = data2.copy()
	data2['Wci(kN·mm)'] = data2['Wci(kN·mm)'].astype('float64')
	data2.loc['合计'] = data2[['Wci(kN·mm)']].agg('sum')
	temp2 = data2.iloc[-1, -1]

	data2.to_excel(new_data, sheet_name=i.replace('全部', 'link'), index=False, float_format='%.2f')

	temp3 = temp2 / (4 * 3.1419 * temp1) * 100
	data3 = pd.DataFrame(data=[[temp2, temp1, temp3]], columns=['阻尼器器耗能(KN·mm)', '结构应变能(kN·mm)', '附加阻尼比(%)'],
						 index=[i[-5] + '向'])

	data3.to_excel(new_data, sheet_name=i[0:-4], float_format='%.2f')

	data_temp = pd.Series({'阻尼器器耗能(KN·mm)': temp2, '结构应变能(kN·mm)': temp1, '附加阻尼比(%)': temp3})

	data_ad = data_ad.append(data_temp, ignore_index=True)

new_index = list(set(index_list))  # 用set去重（去重后顺序是乱的）
new_index.sort(key=index_list.index)

multi_index = pd.MultiIndex.from_product([new_index, ['X向', 'Y向']], names=['工况', '方向'])

data_ad.set_index(multi_index, inplace=True)
avg = data_ad.groupby(by='方向').mean()

avg.index = pd.MultiIndex.from_product([['平均值'], avg.index])
data_ad = pd.concat([data_ad, avg])

doctable(data_ad, '阻尼比', './阻尼比.docx')

data_ad.to_excel(new_data, sheet_name='附加阻尼比', float_format='%.2f')

new_data.save()
new_data.close()

## 写入word

data = pd.read_excel('./数据/09结果-势能和耗能.xlsx', sheet_name='附加阻尼比', header=None)
rows = data.shape[0]
columns = data.shape[1]
doc = Document()
doc.add_paragraph().add_run("附加阻尼比计算汇总表")


doc.sections[0]._sectPr.xpath('./w:cols')[0].set(qn('w:num'), '2') #把第1节设置为2栏
# doc.sections[0].orientation = WD_ORIENTATION.PPORTRAIT # 设置纸张方向为横向，可以不设置 默认为横向
doc.sections[0].page_height = Cm(29.7)  # 设置A3纸的高度
doc.sections[0].page_width = Cm(42)  # 设置A3纸的宽

doc.styles['Normal'].font.name = u'Times New Roman'
doc.styles['Normal']._element.rPr.rFonts.set(qn('w:eastAsia'), u'宋体')

table = doc.add_table(rows, columns, style='Table Grid')
table.alignment = WD_TABLE_ALIGNMENT.CENTER  # 表格居中
for i in range(rows):
	for j in range(columns):
		if str(data.iloc[i, j]) == 'nan':
			table.cell(i, j).merge(table.cell(i - 1, j))
		else:
			table.cell(i, j).text = str(data.iloc[i, j])  # 由于里面有数据型的，需要强制转字符
		table.cell(i, j).vertical_alignment = WD_CELL_VERTICAL_ALIGNMENT.CENTER
		table.cell(i, j).paragraphs[0].alignment = WD_PARAGRAPH_ALIGNMENT.CENTER

# doc.save('./附加阻尼比计算.docx')
for i in table_name:
	data = pd.read_excel('./数据/09结果-势能和耗能.xlsx', sheet_name=i.replace('全部', '势能'), header=None)
	doc.add_paragraph(i[:-4]+'波')
	run_1 = doc.add_paragraph().add_run('1.结构总应变能')
	xlsx2docx(data)
	data = pd.read_excel('./数据/09结果-势能和耗能.xlsx', sheet_name=i[0:-4], header=None)
	run_2 = doc.add_paragraph().add_run('2.附加阻尼比')
	xlsx2docx(data)
	data = pd.read_excel('./数据/09结果-势能和耗能.xlsx', sheet_name=i.replace('全部', 'link'), header=None)
	run_3 = doc.add_paragraph().add_run('3.阻尼器耗能')
	xlsx2docx(data)

for para in doc.paragraphs:
	para.paragraph_format.space_before = Pt(12)
	for run in para.runs:
		run.font.size = Pt(12)
		run.font.bold = True

doc.save('./附加阻尼比计算.docx')
print('附加阻尼比生成成功!')
# print(data)
